const dayjs = require('dayjs')
const db = require('../db/index')
const createExcel = require('../js/exceljs')
// 获取车主列表
exports.carOwnerList = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  const sql = `select * from users where appid=? limit ${size} OFFSET  ${from}`
  db.query(sql, [appid], (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      let sql = `select count(*) from quality where carOwnerId=? and status= 1`
      db.query(sql, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.qualityNum = res[0]['count(*)']
      })
      let sqlinsuranceNum = `select count(*) from insurance where carOwnerId=? and status= 1`
      db.query(sqlinsuranceNum, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.insuranceNum = res[0]['count(*)']
      })
    })
    setTimeout(() => {
      let sql2 = `select count(*) from users where appid=? `
      db.query(sql2, [appid], (err2, res3) => {
        if (err2) return res.cc(err2)
        res.send({
          res: res2,
          total: res3[0]['count(*)']
        })
      })
    }, 500)
  })

}
// 获取全部车主列表
exports.carOwnerListAll = (req, res) => {
  let pageData = req.body
  let appid = pageData.appid
  const sql = `select * from users where appid=? `
  db.query(sql, [appid], (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from users where appid=? `
    db.query(sql2, [appid], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })

}
// 添加车主信息
exports.carOwnerAdd = (req, res) => {
  let data = req.body
  let selectSql = `select * from users where account=?`
  db.query(selectSql, [data.account], (err, res2) => {
    if (err) return res.cc(err)
    if (res2.length > 0) {
      res.send({
        message: '账号重复',
        type: 0
      })
    } else {
      let sql = `insert into users set ?`
      db.query(sql, data, (err, res3) => {
        if (err) return res.cc(err)
        res.send({
          message: '添加成功',
          type: 1
        })
      })
    }
  })
}
// 删除车主信息
exports.carOwnerDelete = (req, res) => {
  let data = req.body
  let selectSql = `delete from users where _id=?`
  db.query(selectSql, [data.id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '删除成功',
      type: 1
    })
  })
}
// 修改用户信息
exports.carOwnerchange = (req, res) => {
  let data = req.body
  let selectSql = `update users set ? where _id=?`
  db.query(selectSql, [data, data._id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '修改成功',
      type: 1
    })
  })
}
// 车主搜索
exports.carOwnerSerach = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  pageData.account = pageData.account ? pageData.account : ''
  pageData.name = pageData.name ? pageData.name : ''
  pageData.phone = pageData.phone ? pageData.phone : ''
  pageData.create_time = pageData.create_time ? dayjs(pageData.create_time).format('YYYY-MM-DD') : ''
  const sql = `select * from users where appid=${appid}  and (account like "%${pageData.account}%" and name like "%${pageData.name}%" and phone like "%${pageData.phone}%" and create_time like "%${pageData.create_time}%") limit ${size} OFFSET  ${from}`
  db.query(sql, [], (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss')
      let sql = `select count(*) from quality where carOwnerId=? `
      db.query(sql, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.qualityNum = res[0]['count(*)']
      })
      let sqlinsuranceNum = `select count(*) from insurance where carOwnerId=?`
      db.query(sqlinsuranceNum, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.insuranceNum = res[0]['count(*)']
      })
    })
    setTimeout(() => {
      let sql2 = `select count(*) from users where appid=? and (account like "%${pageData.account}%" and name like "%${pageData.name}%" and phone like "%${pageData.phone}%" and create_time like "%${pageData.create_time}%")`
      db.query(sql2, [appid], (err2, res3) => {
        if (err2) return res.cc(err2)
        res.send({
          res: res2,
          total: res3[0]['count(*)']
        })
      })
    }, 200)

  })
}
// 车主导出表格
exports.carOwnerCreateExcel = (req, res) => {
  let data = req.body
  columns = [
    { header: '注册时间', key: 'create_time' },
    { header: '车主姓名', key: 'name' },
    { header: '联系方式', key: 'phone' },
    { header: '账号', key: 'account' },
    { header: '密码', key: 'password' },
    { header: '质保信息', key: 'qualityNum' },
    { header: '车险信息', key: 'insuranceNum' },
  ]
  let sql = ``
  if (data.id == 'all') {
    sql = 'select * from users where appid=?'
  } else {
    sql = 'select * from users where appid=? and _id in (?)'
  }
  db.query(sql, [data.appid, data.id], (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss')
      let sql = `select count(*) from quality where carOwnerId=? `
      db.query(sql, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.qualityNum = res[0]['count(*)']
      })
      let sqlinsuranceNum = `select count(*) from insurance where carOwnerId=? `
      db.query(sqlinsuranceNum, [item._id], (err, res) => {
        if (err) return res.cc(err)
        item.insuranceNum = res[0]['count(*)']
      })
    })

    setTimeout(async () => {
      const buffer = await createExcel.createExcel(columns, res2)
      res.setHeader('Content-disposition', 'attachment; filename=file.xlsx')
      res.setHeader('Content-type', 'application/octet-stream')
      res.send(buffer)
    }, 500)


  })

}
// 车主质保列表
exports.carOwnerQualityList = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  const sql = `select * from quality where appid=? and carOwnerId=? limit ${size} OFFSET  ${from}`
  db.query(sql, [appid, pageData.carOwnerId], (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from quality where appid=? and carOwnerId=?`
    db.query(sql2, [appid, pageData.carOwnerId], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}
// 导出车主质保表格
exports.carOwnerQualityCreateExcel = (req, res) => {
  let data = req.body
  columns = [
    { header: '质保上传时间', key: 'workTime' },
    { header: '产品类型', key: 'filmType' },
    { header: '产品名称', key: 'filmName' },
    { header: '施工门店', key: 'storeWork' },
    { header: '车主姓名', key: 'ownerName' },
    { header: '联系方式', key: 'ownerPhone' },
    { header: '车牌号', key: 'carNo' },
    { header: '车架号', key: 'carVIN' },
    { header: '施工部位', key: 'carApart' },
    { header: '质保年限', key: 'filmAge' }
  ]
  let sql = ``
  if (data.id == 'all') {
    sql = 'select * from quality where carOwnerId=?'
  } else {
    sql = 'select * from quality where carOwnerId=? and _id in (?)'
  }
  db.query(sql, [data.carOwnerId, data.id], async (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      item.workTime = dayjs(item.workTime).format('YYYY-MM-DD HH:mm:ss')
    })
    const buffer = await createExcel.createExcel(columns, res2)
    res.setHeader('Content-disposition', 'attachment; filename=download.xlsx')
    res.setHeader('Content-type', 'application/octet-stream')
    res.send(buffer)
  })

}
// 条件车主质保查询
exports.carOwnerQualitySerach = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  pageData.filmNo = pageData.filmNo ? pageData.filmNo : ''
  pageData.carVIN = pageData.carVIN ? pageData.carVIN : ''
  pageData.workTime = pageData.workTime ? pageData.workTime : ''
  pageData.filmType = pageData.filmType ? pageData.filmType : ''
  pageData.carName = pageData.carName ? pageData.carName : ''
  pageData.filmName = pageData.filmName ? pageData.filmName : ''
  const sql = `select * from quality where appid=${appid}  and carOwnerId=${pageData.carOwnerId} and (filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.workTime}%" and filmType like "%${pageData.filmType}%" and carName like "%${pageData.carName}%" and filmName like "%${pageData.filmName}%")  limit ${size} OFFSET  ${from}`
  db.query(sql, (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from quality where appid=? and carOwnerId=${pageData.carOwnerId} and (filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and workTime like "%${pageData.workTime}%" and filmType like "%${pageData.filmType}%" and carName like "%${pageData.carName}%" and filmName like "%${pageData.filmName}%")`
    db.query(sql2, [appid], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}
// 获取车主保险列表
exports.carOwnerInsuranceList = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  const sql = `select * from insurance where appid=? and carOwnerId=? and status= 1 limit ${size} OFFSET  ${from}`
  db.query(sql, [appid, pageData.carOwnerId], (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from insurance where appid=? and carOwnerId=? and status= 1`
    db.query(sql2, [appid, pageData.carOwnerId], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}
// 添加车主保险
exports.carOwnerInsuranceAdd = (req, res) => {
  let data = req.body
  data.imageList = JSON.stringify(data.imageList)
  data.region = JSON.stringify(data.region)
  data.status = 0
  data.warrantyNum = `Qud${dayjs().format('YYYYMMDDHHmmss')}`
  let sql = `insert into insurance set ?`
  db.query(sql, [data], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '添加成功'
    })
  })
}
// 修改
exports.carOwnerInsuranceChange = (req, res) => {
  let data = req.body
  data.imageList = JSON.stringify(data.imageList)
  data.region = JSON.stringify(data.region)
  let sql = `update insurance set ? where _id=?`
  db.query(sql, [data, data._id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '修改成功'
    })
  })
}
// 删除车主保险
exports.carOwnerInsuranceDelete = (req, res) => {
  let data = req.body
  let sql = `delete from insurance where _id=?`
  db.query(sql, [data.id], (err, res2) => {
    if (err) return res.cc(err)
    res.send({
      message: '删除成功'
    })
  })
}
// 导出信息
exports.carOwnerInsuranceCreateExcel = (req, res) => {
  let data = req.body
  columns = [
    { header: '车险上传时间', key: 'create_time' },
    { header: '投保人姓名', key: 'carOwnerName' },
    { header: '身份证号', key: 'identity' },
    { header: '保单号', key: 'warrantyNum' },
    { header: '联系方式', key: 'carOwnerPhone' },
    { header: '所在区域', key: 'region' },
    { header: '车辆品牌型号', key: 'carName' },
    { header: '车牌号', key: 'carNo' },
    { header: '车架号', key: 'carVIN' },
    { header: '投保产品', key: 'filmName' },
    { header: '状态', key: 'status' },
    { header: '保险费', key: 'price' }
  ]
  let sql = ``
  if (data.id == 'all') {
    sql = 'select * from insurance where carOwnerId=?'
  } else {
    sql = 'select * from insurance where carOwnerId=? and _id in (?)'
  }
  db.query(sql, [data.carOwnerId, data.id], async (err, res2) => {
    if (err) return res.cc(err)
    res2.forEach((item) => {
      item.status = item.status == 0 ? '生效' : '失效'
      item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss')
    })
    const buffer = await createExcel.createExcel(columns, res2)
    res.setHeader('Content-disposition', 'attachment; filename=download.xlsx')
    res.setHeader('Content-type', 'application/octet-stream')
    res.send(buffer)
  })
}
// 条件车衣保险查询
exports.carOwnerInsuranceSerach = (req, res) => {
  let pageData = req.body
  let page = pageData.page * 1 - 1
  let size = pageData.size * 1
  let from = page * size
  let appid = pageData.appid
  pageData.filmNo = pageData.filmNo ? pageData.filmNo : ''
  pageData.carVIN = pageData.carVIN ? pageData.carVIN : ''
  pageData.warrantyNum = pageData.warrantyNum ? pageData.warrantyNum : ''
  pageData.filmName = pageData.filmName ? pageData.filmName : ''
  pageData.carName = pageData.carName ? pageData.carName : ''
  pageData.filmType = pageData.filmType ? pageData.filmType : ''
  const sql = `select * from insurance where appid=${appid} and carOwnerId=${pageData.carOwnerId} and(filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and warrantyNum like "%${pageData.warrantyNum}%" and filmName like "%${pageData.filmName}%"and carName like "%${pageData.carName}%" and filmType like "%${pageData.filmType}%" ) limit ${size} OFFSET  ${from}`
  db.query(sql, (err, res2) => {
    if (err) return res.cc(err)
    let sql2 = `select count(*) from insurance where appid=? and carOwnerId=${pageData.carOwnerId} and (filmNo like "%${pageData.filmNo}%" and carVIN like "%${pageData.carVIN}%" and warrantyNum like "%${pageData.warrantyNum}%" and filmName like "%${pageData.filmName}%"and carName like "%${pageData.carName}%" and filmType like "%${pageData.filmType}%" )`
    db.query(sql2, [appid], (err2, res3) => {
      if (err2) return res.cc(err2)
      res.send({
        res: res2,
        total: res3[0]['count(*)']
      })
    })
  })
}